package com.huayi.website.repository;

import com.huayi.website.entity.EnumPages;
import com.huayi.website.entity.NewsCategoryEntity;
import com.huayi.website.entity.PagesEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

@Repository
public class PagesRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<PagesEntity> getAllPage(){
        return jdbcTemplate.query("SELECT * FROM "+Tables.Pages+" ORDER BY id asc",new BeanPropertyRowMapper<>(PagesEntity.class));
    }


    public PagesEntity getById(Integer pageId){
        String sql ="SELECT * FROM "+Tables.Pages+" WHERE id=?";
        try{
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(PagesEntity.class),pageId);
        }catch (Exception e){
            return new PagesEntity();
        }
    }
    @Transactional
    public Integer editPage(Integer pageId, String name, String titleCn, String titleEn){
        if(pageId!=null && pageId>0){
            //修改
            String sql = "UPDATE "+Tables.Pages+" SET `name`=?,title_en=?,title_cn=? WHERE id=?";
            jdbcTemplate.update(sql,name,titleEn,titleCn,pageId);
            return pageId;
        }else{
            //新增
            //添加
            String sql ="INSERT INTO "+Tables.Pages+" (`name`,title_en,title_cn) VALUE (?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, name);
                    ps.setString(2, titleEn);
                    ps.setString(3, titleCn);

                    return ps;
                }
            }, keyHolder);

            Integer id = keyHolder.getKey().intValue();
            return id;
        }
    }

}
